import os 
import xlrd
import pandas as pd
import shutil



files=[]
# 迭代查找多成目录文件
def get_file_path(path):
    #是目录则查找目录继续查找
    if os.path.isdir(path):
        for file in os.listdir(path):
            get_file_path(path+'\\'+file)
    else:
        #是文件则加入列表
        files.append(path)
    pass

#读取execl
def get_table_names(filename):
    sets=set()
    df_table=pd.read_excel(filename,sheet_name='Sheet3',usecols=['dataser脚本'],keep_default_na=False)
    for item in df_table.values:
        sets.add(item[0].lower())
    return sets

path='C:\\Users\\me\\Downloads\\营销日报迁移\\营销日报迁移\\脚本'
xmlFile='C:\\Users\\me\Downloads\\指标梳理-YDM.xlsx'
out_path='C:\\Users\\me\\Desktop\\工作日志\\大数据平台\\华为poc\\脚本1'

if __name__ == '__main__':
    s=get_table_names(xmlFile)
    get_file_path(path)
    for f in files:
        if f.endswith('.sql'):
            print(f)
            splits=f.split('.sql')[0].split('\\')
            last_split=splits[len(splits)-1]
            if s.__contains__(last_split.lower()):
                target_path=out_path+"\\"+last_split+'.sql'
                shutil.copyfile(f,target_path)
    pass
    